﻿


CREATE PROCEDURE [dbo].[GenOrdonantariPlata]   
@idPlata int,
@idOrdonantare int OUTPUT 
AS
DECLARE @cate int

SELECT @cate = COALESCE(IDJurnal,-1) FROM DocumentePlata dp JOIN DocumentePlataDetaliu dpd ON dp.IDDocumentPlata =  dpd.IDDocumentPlata
	JOIN Documente d ON d.IDDocument = dpd.IDDocument		
	JOIN DocumenteDetaliu dd ON d.IDDocument = dd.IDDocument
	WHERE dp.IDDocumentPlata = @idPlata 

IF @cate=-1
	GOTO EROARE3
	
DECLARE @cate2 varchar(200)

SELECT @cate2 = COALESCE(MAX(dd.ArtAl),'') FROM DocumentePlata dp JOIN DocumentePlataDetaliu dpd ON dp.IDDocumentPlata =  dpd.IDDocumentPlata
	JOIN Documente d ON d.IDDocument = dpd.IDDocument		
	JOIN DocumenteDetaliu dd ON d.IDDocument = dd.IDDocument
	WHERE dp.IDDocumentPlata = @idPlata 

IF @cate2=''
	GOTO EROARE4

DECLARE @idResponsabilImplicit int
SELECT @idResponsabilImplicit = IDResponsabil  FROM ResponsabilVizaCFP


IF @idResponsabilImplicit IS NULL
	GOTO EROARE5



SELECT @cate = COUNT(*) FROM [Ordonantari] ord WHERE ord.IDDocumentPlata = @idPlata
--sterge ordonantarile care au fost generate
IF @cate<>0
BEGIN
	PRINT('Sterge ordonantarile existente')	
	DELETE FROM [Ordonantari] WHERE IDDocumentPlata = @idPlata
	IF @@ERROR != 0 GOTO EROARE2				
END


PRINT('Ordonontare noua')
INSERT INTO [Ordonantari]
   ([NrDoc],[DataDoc],[IDFurnizor],[ValoareLei],[IDJurnal],[Scop],[DocJust],[AngLegal],[Achitat],[CodUnitate]
   ,[IdModPlata],[TotalDatorat],[TotalAvansuri],[ArtAl],[VizatCFP],[MotivRefuz],[IDResponsabil]
   ,[DataVizaCFP],[NrRegCFP],[IBANSursa],[IBANDest],[IDDocumentPlata])
	SELECT  (SELECT COALESCE(MAX(NrDoc),0)+1 FROM Ordonantari) AS NrDoc,
	dp.Data AS DataDoc, 
	dp.IDPartener AS IDFurnizor,
	SUM(dd.ValoareTotala +dd.ValoareTVA) As ValoareLei,
	d.IDJurnal AS IDJurnal,
	'PLATA ' + MAX(dp.Numar) + '/' +CAST(DAY(MAX(dp.Data)) AS Varchar(2)) +'.' + CAST(MONTH(MAX(dp.Data)) AS Varchar(2)) + '.' + CAST(YEAR(MAX(dp.Data)) AS Varchar(4)) AS Scop,
	'FACT ' + MAX(d.NrDoc) + '/' +CAST(DAY(MAX(d.DataDoc)) AS Varchar(2)) +'.' + CAST(MONTH(MAX(d.DataDoc)) AS Varchar(2)) + '.' + CAST(YEAR(MAX(d.DataDoc)) AS Varchar(4))  AS [DocJust],
	'ANG LEGAL'	AS [AngLegal],
	0 AS Achitat,
	MAX(d.CodUnitate) AS [CodUnitate],
	(SELECT MIN(IDModPlata) FROM ModPlata) AS [IdModPlata],
	SUM(dd.ValoareTotala + dd.ValoareTVA) As [TotalDatorat],
	0 AS [TotalAvansuri],
	MAX(dd.ArtAl) aS [ArtAl],
	1 AS [VizatCFP],
	'' AS [MotivRefuz],
	(SELECT COALESCE(MAX(IDResponsabil),@idResponsabilImplicit) FROM Ordonantari)  AS [IDResponsabil],
	dp.Data AS [DataVizaCFP],
	(SELECT COALESCE(MAX(NrRegCFP),0)+1 FROM Ordonantari) AS NrRegCFP,
	MAX(j.IBAN) AS [IBANSursa],
	MAX(f.ContBancar) AS [IBANDest],
	@idPlata AS	[IDDocumentPlata]
	FROM DocumentePlata dp JOIN DocumentePlataDetaliu dpd ON dp.IDDocumentPlata =  dpd.IDDocumentPlata
	JOIN Documente d ON d.IDDocument = dpd.IDDocument		
	JOIN DocumenteDetaliu dd ON d.IDDocument = dd.IDDocument
	JOIN Jurnale j ON j.CodUnitate = d.CodUnitate AND d.IDJurnal = j.IDJurnal
	JOIN Parteneri f ON d.IDPartener = f.IDPartener
	WHERE dp.IDDocumentPlata = @idPlata 
	GROUP BY dp.IDPartener,d.IDJurnal,dd.ArtAl,dp.Data
	IF @@ERROR != 0 GOTO EROARE		
	SET @idOrdonantare = @@IDENTITY	
	





GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea ordonantarii',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('Nu se poate seta ordonantarea pentru plata',16,-1)
	GOTO Gata
EROARE2:
	RAISERROR ('Nu pot sterge ordonantarile generate',16,-1)
	GOTO Gata
EROARE3:
	RAISERROR ('Factura nu are trecut capitolul!',16,-1)
	GOTO Gata
EROARE4:
	RAISERROR ('Factura nu are specificate articolule sau aliniatele pe detaliere!',16,-1)
	GOTO Gata
EROARE5:
	RAISERROR ('Nu a fost definit nici un responsabil pentru viza CFP!',16,-1)
	GOTO Gata



Gata: